﻿#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
import re
import codecs
import MySQLdb
import time
from datetime import date
from datetime import timedelta
import smtplib

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.header import Header


def main():
	# parse command line options
#	filename = sys.argv[1]
#	buildnum = sys.argv[2]
	
	filename = "statistics.html"
	
	week1 = timedelta(days=7)
	today = date.today()
	lastweek = today - week1
	
	strtoday = today.isoformat()
	strlastweek = lastweek.isoformat()
	
	f = codecs.open(filename, "w", "utf-8")
#	f = open(filename, "w")
	f.write("")
	f.close()
	
	f = codecs.open(filename, "a", "utf-8")
#	f = open(filename, "w")
	f.write(u'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">')
	f.write(u'<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">')
	f.write(u'<head>')
	f.write(u'<meta http-equiv="content-type" content="text/html; charset=utf-8" />')
	f.write(u'<title>PMS 활동 분석</title>')
	f.write(u'</head>')
	f.write(u'</body>')
	f.write(u'PMS 활동 분석<br />')
	f.write(strlastweek + ' ~ ' + strtoday + '<br />')
	f.write(u'<table border=1>')
	f.write(u'<tr>')
	f.write(u'<th>성명</th><th>맡은일감</th><th>금주일감생성</th><th>금주일감수정</th><th>총일감생성</th><th>총일감수정</th>')
	f.write(u'</tr>')
	
	// redmine mysql db ip : localhost
	// redmine db name : redmine
	// mysql id/pw : root/password
	db = MySQLdb.connect(host="localhost", db="redmine" ,user="root",passwd="password", charset='utf8')
	cur = db.cursor()
#	cur.execute("SELECT * FROM friends")
	query = "SELECT id, lastname FROM users WHERE status=1 and type='Group' order by lastname"
	cur.execute(query)
	
	groups = cur.fetchall()
	for group in groups:
		groupid = str(group[0])
		f.write(u'<tr><td>' + group[1] + u'</td></tr>')

		query = "SELECT id, mail, lastname, firstname FROM users, groups_users WHERE users.id = groups_users.user_id and groups_users.group_id = '" + groupid + "' and status=1 and type='User' and mail like '%@iniline.co.kr%' order by lastname, firstname"
		cur.execute(query)
		
	#	ret = cur.fetchall()
	#	print ret
		mailto = ""
		mailtomsg = ""
		users = cur.fetchall()
		for user in users:
			userid = str(user[0])
			f.write(u'<tr>')
			f.write(u'<td>' + user[2] + ' ' + user[3] + u'</td>')
	#		f.write(u'<td>' + user[1] + u'</td>')

			mailto += user[1].encode('ascii', 'ignore') + " "
			mailtomsg += user[1] + ", "

			# 맡은일감
			query = "SELECT COUNT(*), assigned_to_id FROM issues WHERE assigned_to_id='" + userid + "' and status_id != 5 AND status_id != 6 GROUP BY assigned_to_id"
			cur.execute(query)
			cnt = cur.fetchone()
			if cnt == None:
				print user[0], " - ", user[1], " - 0"
				value = 0
			else:
				print user[0], " - ", user[1], " - ", cnt[0]
				value = cnt[0]
				
			if value > 0:
				f.write(u'<td align=right><b>' + str(value) + u'</b></td>')
			else:
				f.write(u'<td align=right>' + str(value) + u'</td>')

			# 금주일감생성
			query = "SELECT COUNT(*), author_id FROM issues WHERE author_id='" + userid + "' and created_on > '" + strlastweek + "' GROUP BY author_id"
			cur.execute(query)
			cnt = cur.fetchone()
			if cnt == None:
				print user[0], " - ", user[1], " - 0"
				value = 0
			else:
				print user[0], " - ", user[1], " - ", cnt[0]
				value = cnt[0]
				
			if value > 0:
				f.write(u'<td align=right><b>' + str(value) + u'</b></td>')
			else:
				f.write(u'<td align=right>' + str(value) + u'</td>')
			
			# 금일일감수정
			query = "SELECT COUNT(*), user_id FROM journals WHERE user_id='" + userid + "' and created_on > '" + strlastweek + "' GROUP BY user_id"
			cur.execute(query)
			cnt = cur.fetchone()
			print "금일일감수정"
			if cnt == None:
				print user[0], " - ", user[1], " - 0"
				value = 0
			else:
				print user[0], " - ", user[1], " - ", cnt[0]
				value = cnt[0]
				
			if value > 0:
				f.write(u'<td align=right><b>' + str(value) + u'</b></td>')
			else:
				f.write(u'<td align=right>' + str(value) + u'</td>')

			# 총일감생성
			query = "SELECT COUNT(*), author_id FROM issues WHERE author_id='" + userid + "' GROUP BY author_id"
			cur.execute(query)
			cnt = cur.fetchone()
			if cnt == None:
				print user[0], " - ", user[1], " - 0"
				value = 0
			else:
				print user[0], " - ", user[1], " - ", cnt[0]
				value = cnt[0]
				
			if value > 0:
				f.write(u'<td align=right><b>' + str(value) + u'</b></td>')
			else:
				f.write(u'<td align=right>' + str(value) + u'</td>')
		
			# 총일감수정
			query = "SELECT COUNT(*), user_id FROM journals WHERE user_id='" + userid + "' GROUP BY user_id"
			cur.execute(query)
			cnt = cur.fetchone()
			print "금일일감수정"
			if cnt == None:
				print user[0], " - ", user[1], " - 0"
				value = 0
			else:
				print user[0], " - ", user[1], " - ", cnt[0]
				value = cnt[0]
				
			if value > 0:
				f.write(u'<td align=right><b>' + str(value) + u'</b></td>')
			else:
				f.write(u'<td align=right>' + str(value) + u'</td>')

			f.write(u'</tr>')
		
	f.write(u'</table>');
	f.write(u'</body></html>')
	f.close()

	fromaddr = "from@test.co.kr"
	toaddrs = mailto.split()

	print "toaddrs = ", toaddrs
	
	subject = u"PMS 활동 분석"
	
	# Create message container - the correct MIME type is multipart/alternative.
	msg = MIMEMultipart('alternative')
	msg['Subject'] = Header(s=subject, charset="utf-8")
	msg['From'] = me
	msg['To'] = mailtomsg

	f = codecs.open(filename, "r", "utf-8")
	ff = f.read()
	f.close()
	
	# Create the body of the message (a plain-text and an HTML version).
	text = ""
	html = ff
#	print text
#	print html

	# Record the MIME types of both parts - text/plain and text/html.
	part1 = MIMEText(text, 'plain')
	part2 = MIMEText(html, 'html', _charset="utf-8")

	# Attach parts into message container.
	# According to RFC 2046, the last part of a multipart message, in this case
	# the HTML message, is best and preferred.
	msg.attach(part1)
	msg.attach(part2)

	# Send the message via local SMTP server.
	s = smtplib.SMTP('localhost')
	# sendmail function takes 3 arguments: sender's address, recipient's address
	# and message to send - here it is sent as one string.
	# 메일서버에 로그인이 필요할 경우
	s.login("id", "passwrod")
	s.sendmail(fromadddr, toaddrs, msg.as_string())
	s.quit()	
	
if __name__ == "__main__":
    main()